from DAL.sqlHelper import get_keyboard_conn as conn_a,SQL_ADD_FIELD_VALUES,SQL_UPDATE_SET

class loginDAL:
    def login(self,account,password):
        sql = """
                SELECT id from login_users
                WHERE account='{0}' AND  `password`='{1}' AND `state` < 2
                """
        sql_update_token = """
                UPDATE login_users
                SET token=UUID(),
                    state=1
                WHERE account='{0}' AND  `password`='{1}' AND `state` < 2              
                """
        sql_select = """
                SELECT id,account,token,state,is_admin from login_users
                WHERE account='{0}' AND  `password`='{1}' AND `state` < 2        
        """
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                sql1 = sql.format(account, password)
                cursor.execute(sql1)
                conn.commit()
                valid = cursor.fetchone()
                if valid:  # # 更新token
                    sql2 = sql_update_token.format(account, password)
                    # print(sql2)
                    cursor.execute(sql2)
                    conn.commit()
                    sql3 = sql_select.format(account, password)
                    cursor.execute(sql3)
                    conn.commit()
                    res = cursor.fetchone()
                else:
                    res = None
                return res
        finally:
            conn.close()


    def getUserInfo(self,token=""):
        if not token:
            return None
        sql = """    SELECT id,account,token,state,is_admin from login_users
                     WHERE token='{0}' AND `state` = 1   
               """
        # 获取模块权限guid
        sql_right ="""
                SELECT DISTINCT guid
                FROM (
                SELECT guid
                FROM  login_module AS A
                JOIN  login_users_module_ref AS B
                on A.id = B.module_id
                JOIN   login_users  AS C
                on B.user_id = C.id
                WHERE C.token ='{0}'
                UNION
                SELECT guid
                FROM  login_module AS U
                JOIN  login_role_module_ref AS V
                on U.id = V.module_id
                JOIN  login_users_role_ref   AS W
                on  V.role_id = W.role_id
                JOIN login_users AS X
                on   W.user_id=X.id
                WHERE X.token ='{0}'
                ) AS H
        """

        # 获取用户具体信息
        sql_user_info ="""
        SELECT id,`name`,`department`,`group`,jurisdiction,pid,equipment,bind_account,DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') AS create_time
        FROM `org_team_members`
        WHERE bind_account='{0}'
        """
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                sql1 = sql.format(token)
                cursor.execute(sql1)
                conn.commit()
                res = cursor.fetchone()
                if res:
                    sql2 = sql_right.format(token)
                    # print(sql2)
                    cursor.execute(sql2)
                    conn.commit()
                    res1 = cursor.fetchall()
                    if res1:
                        res11 = [value["guid"] for value in res1]
                        res["modules"] = res11
                    else:
                        res["modules"] = []
                    sql3 = sql_user_info.format(res['account'])
                    cursor.execute(sql3)
                    conn.commit()
                    res2 = cursor.fetchone()
                    if res2:
                        res["user_info_exp"] = res2
                    else:
                        res["user_info_exp"] = {}
                return res
        finally:
            conn.close()

    def loginByToken(self,token=""):
        if not token:
            return None
        sql = """    SELECT id,account,token,state,is_admin from login_users
                     WHERE token='{0}' AND `state` = 1   
               """
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                sql1 = sql.format(token)
                cursor.execute(sql1)
                conn.commit()
                res = cursor.fetchone()
                return res
        finally:
            conn.close()

    def loginOut(self,token=""):
        if not token:
            return 0
        sql = """
                UPDATE login_users
                SET token='',
                    state=0
                WHERE token='{0}' AND `state`=1   
                """
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                sql1 = sql.format(token)
                cursor.execute(sql1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()



if __name__ == "__main__":
     # 调试部分---------------------------
     dal = loginDAL()
     res = dal.getUserInfo('8a1fb395-115a-11ef-a12f-00163e0acd8d')
     print(res)

     # res = dal.login('admin','123456')
     # res =  dal.loginByToken('caac5376-d4b4-4c2c-80a3-3ae655ff0561')
     # res = dal.loginOut('caac5376-d4b4-4c2c-80a3-3ae655ff0561')
     # print(res)


